from sqlalchemy import Column, String, create_engine, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
MYSQL_URI = "mysql+pymysql://root:mysql@127.0.0.1:3306/testdb?charset=utf8mb4"
engine = create_engine(MYSQL_URI)
DBSession = sessionmaker(bind=engine)


class Product(Base):
    __tablename__ = 'product'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    class_name = Column(String(68))


# 增加
session = DBSession()
new_product = Product(id=123, name='产品名称', class_name='food')
session.add(new_product)
session.commit()

# 查询
prod = session.query(Product).filter(Product.id == '123').one()
print('name:', prod.name)

# 更新
session.query(Product).filter_by(id='123').update(name='生鲜食品')
session.commit()

# 删除
session.query(Product).filter(Product.id == '123').delete()
session.commit()


# 最后关闭session
session.close()


